/*
 * Group:PrePool K9-357 
 * 8/2010
 */
package dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import util.Controller;

import dto.WordDTO;

/**
 *
 */
public class WordDAO {
	Logger LOG = Logger.getLogger("WordDAO");

	/**
	 * 
	 * @param word
	 *            word's name
	 * @param meaning
	 *            word's meaning
	 * @param dictID
	 *            id of destination dictionary
	 * @param status
	 * @return true if successful,false if fail
	 */
	public boolean addWord(String word, String meaning, int dictID, int status) {
		Connection conn = util.Controller.getConnection();
		CallableStatement pstat = null;
		String query = "{call m_word_insert(?,?,?,?)}";

		try {
			pstat = conn.prepareCall(query);
			pstat.setString(1, word);
			pstat.setString(2, meaning);
			pstat.setInt(3, dictID);
			pstat.setInt(4, status);
			pstat.executeUpdate();
		} catch (SQLException e) {
			return false;
		}
		return true;
	}

	/**
	 * function delete word
	 * 
	 * @param id
	 *            id of word
	 * @return true if success
	 */
	public boolean deleteWord(int id) {
		Connection conn = util.Controller.getConnection();
		CallableStatement pstat = null;
		String query = "{call m_word_delete(?)}";

		try {
			pstat = conn.prepareCall(query);
			pstat.setInt(1, id);
			pstat.execute();
		} catch (SQLException e) {
			return false;
		}
		return true;
	}

	/**
	 * update meaning of a word
	 * 
	 * @param id
	 *            word's id
	 * @param meaning
	 *            meaning of word want to update
	 * @return true if success
	 */
	public boolean updateWord(int id, String meaning) {
		Connection conn = util.Controller.getConnection();
		PreparedStatement pstat = null;
		String query = "{call update_word_meaning(?)}";

		try {
			pstat = conn.prepareStatement(query);
			pstat.setInt(1, id);
			pstat.setString(2, meaning);
			pstat.execute();
		} catch (SQLException e) {
			return false;
		}
		return true;
	}

	/**
	 * function update status of word
	 * 
	 * @param id
	 *            id of word
	 * @param status
	 *            status want to update
	 * @return true if success.
	 */
	public boolean updateWord(int id, int status) {
		Connection conn = util.Controller.getConnection();
		CallableStatement pstat = null;
		String query = "{call update_word_status(?,?)}";
		try {
			pstat = conn.prepareCall(query);
			pstat.setInt(1, id);
			pstat.setInt(2, status);
			pstat.execute();
		} catch (SQLException e) {
			return false;
		}
		return true;
	}

	/**
	 * @param dictID
	 * @return
	 */
	public int getNumSuggestedWord(int dictID) {
		return getNumWord(dictID, 0);
	}

	/**
	 * @param dictID
	 * @return
	 */
	public int getNumReviewedWord(int dictID) {
		return getNumWord(dictID, 1);
	}

	/**
	 * @param dictId
	 *            dictionary's Id
	 * @param status
	 * @return
	 */
	public static List<WordDTO> getWords(int dictId, int status) {

		Connection con = Controller.getConnection();
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<WordDTO> list = new ArrayList<WordDTO>();

		try {
			pstat = con
					.prepareStatement("select m_id, m_word, m_meaning from m_word where m_dictID = ? and m_status = ?");
			pstat.setInt(1, dictId);
			pstat.setInt(2, status);
			rs = pstat.executeQuery();

			while (rs.next()) {
				WordDTO word = new WordDTO();
				word.setId(rs.getInt("m_id"));
				word.setWord(rs.getString("m_word"));
				word.setMeaning(rs.getString("m_meaning"));
				word.setDictID(dictId);
				word.setStatus(status);

				list.add(word);
			}
		} catch (Exception e) {
			// TODO: handle exception
		}

		return list;
	}

	/**
	 * @param dictId
	 *            dictionary's Id
	 * @param status
	 * @return number of words in dictionary
	 */
	private int getNumWord(int dictId, int status) {
		Connection conn = Controller.getConnection();
		PreparedStatement pstat = null;
		int count = 0;
		ResultSet rs = null;
		String query = "select count(*) as num from m_word where m_dictID = ? AND m_status = ?";
		
		try {
			pstat = conn.prepareStatement(query);
			pstat.setInt(1, dictId);
			pstat.setInt(2, status);
			rs = pstat.executeQuery();

			rs.next();
			count = rs.getInt("num");
		} catch (SQLException e) {

		}
		return count;
	}
	
	public static WordDTO getWord(int wordID) {

		WordDTO wordDTO = new WordDTO();
		Connection conn = Controller.getConnection();
		PreparedStatement pstat = null;
		ResultSet rs = null;
		String query = "select * from m_word where m_id = ?";

		try {
			pstat = conn.prepareStatement(query);
			pstat.setInt(1, wordID);
			
			rs = pstat.executeQuery();
			rs.next();
			wordDTO.setId(wordID);
			wordDTO.setDictID(rs.getInt("m_dictID"));
			wordDTO.setMeaning(rs.getString("m_meaning"));
			wordDTO.setStatus(rs.getInt("m_status"));
			wordDTO.setWord(rs.getString("m_word"));
		} catch (Exception e) {
			// TODO: handle exception
		}
		return wordDTO;
	}
}
